package database;

import item.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ItemTable extends Table {

	private static final String ITEM_INSERT = "INSERT INTO Item VALUES (%s, '%s', '%s', '%s', '%s', %s, %s)";
	private static final String ITEM_DELETE = "DELETE FROM Item WHERE upc=%s";
	private static final String ITEM_DISPLAY = "SELECT * FROM Item";
	
	private static final String ITEM_PRICE_UPDATE = "UPDATE Item SET sellPrice = %s WHERE upc = %s";
	
	private static final String ITEM_UPC_QUERY = "SELECT * FROM Item WHERE upc=%s";
	private static final String ITEM_CATEGORY_QUERY = "SELECT * FROM Item WHERE category='%s'";
	private static final String ITEM_TITLE_QUERY = "SELECT * FROM Item WHERE title='%s'";
	
	private static final String ITEM_EXISTS_QUERY = "SELECT * FROM Item WHERE upc=%s";
	
	public enum Column
	{
		UPC(1, true),
		TITLE(2, false),
		TYPE(3, false),
		CATEGORY(4, false),
		COMPANY(5, false),
		YEAR(6, false),
		SELL_PRICE(7, false);
		
		private int index;
		private boolean isPrimaryKey;
		
		private String[] columnNames = {
				"upc",
				"title",
				"type",
				"category",
				"company",
				"year",
				"sellPrice"};
		
		private Column(int index, boolean isPrimaryKey) 
		{
			this.index = index;
			this.isPrimaryKey = isPrimaryKey;
		}
		
		public int getIndex()
		{
			return index;
		}
		
		public String toString()
		{
			return columnNames[this.ordinal()];
		}

		public boolean isPrimaryKey() {
			return this.isPrimaryKey;
		}
	};
	
	public ItemTable()
	{
		super(ITEM_INSERT, ITEM_DELETE, ITEM_DISPLAY, ITEM_EXISTS_QUERY);
	}
		
	public Item getItemFromUpc(String upc) throws SQLException
	{
		String fmtdQuery = String.format(ITEM_UPC_QUERY, upc);
		ResultSet rs = dbCon.executeQuery(fmtdQuery);
		
		return getItemFromResultSet(rs);
	}
	
	public List<Item> getItemsFromCategory(String category) throws SQLException
	{
		String fmtdQuery = String.format(ITEM_CATEGORY_QUERY, category);
		ResultSet rs = dbCon.executeQuery(fmtdQuery);
		
		return getItemsFromResultSet(rs);
	}
	
	public List<Item> getItemsFromTitle(String title) throws SQLException
	{
		String fmtdQuery = String.format(ITEM_TITLE_QUERY, title);
		ResultSet rs = dbCon.executeQuery(fmtdQuery);
		
		return getItemsFromResultSet(rs);
	}
	
	private List<Item> getItemsFromResultSet(ResultSet rs) throws SQLException
	{
		List<Item> items = new ArrayList<Item>();
		if(rs != null)
		{
			while(rs.next())
			{
				String upc = rs.getString(Column.UPC.getIndex());
				String title = rs.getString(Column.TITLE.getIndex());
				String type = rs.getString(Column.TYPE.getIndex());
				String category = rs.getString(Column.CATEGORY.getIndex());
				String company = rs.getString(Column.COMPANY.getIndex());
				int year = rs.getInt(Column.YEAR.getIndex());
				float sellPrice = rs.getFloat(Column.SELL_PRICE.getIndex());
				
				Item curItem = new Item(upc, title, type, category, company, year, sellPrice);
				items.add(curItem);
			}
			
			rs.close();
		}
		
		return items;
	}
	
	private Item getItemFromResultSet(ResultSet rs) throws SQLException
	{
		Item item = null;
		if(rs != null)
		{
			if(rs.next())
			{
				String upc = rs.getString(Column.UPC.getIndex());
				String title = rs.getString(Column.TITLE.getIndex());
				String type = rs.getString(Column.TYPE.getIndex());
				String category = rs.getString(Column.CATEGORY.getIndex());
				String company = rs.getString(Column.COMPANY.getIndex());
				int year = rs.getInt(Column.YEAR.getIndex());
				float sellPrice = rs.getFloat(Column.SELL_PRICE.getIndex());
				
				item = new Item(upc, title, type, category, company, year, sellPrice);
			}
			
			rs.close();
		}
		
		return item;
	}
	
	public void updateSellPrice(String upc, float newSellPrice) throws SQLException
	{
		String fmtedQuery = String.format(ITEM_PRICE_UPDATE, String.valueOf(newSellPrice), upc);
		dbCon.executeStatement(fmtedQuery);
	}

	@Override
	public List<String> getPrimaryKeyValues(List<String> insertValues) {
		List<String> primaryKeyValues = new ArrayList<String>();
		for(Column col : Column.values())
		{
			if(col.isPrimaryKey())
			{	
				primaryKeyValues.add(insertValues.get(col.ordinal()));
			}
		}				
		
		return primaryKeyValues;
	}
	
	@Override
	public List<String> getColumnNames() {
		Column columns[] = Column.values();
		
		List<String> columnNames = new ArrayList<String>(columns.length);
		for( Column col : columns)
		{
			columnNames.add(col.ordinal(), col.toString());
		}
		return columnNames;
	}

	@Override
	public List<String> getPrimaryKeyColumnNames() {
		Column columns[] = Column.values();
		
		List<String> primaryKeyColumnNames = new ArrayList<String>(columns.length);
		for( Column col : columns)
		{
			if(col.isPrimaryKey())
			{
				primaryKeyColumnNames.add(col.ordinal(), col.toString());
			}
		}
		return primaryKeyColumnNames;
	}
}
